Imports WRDB.DBProviders

''' <summary>
''' Each WRDB project has a unique connection to a database and specified preferences
''' </summary>
''' <remarks>A separate class is used because, although an instance of WRDB can have only one project active at a time, it is sometimes necessary to open another one to copy tables from, etc.</remarks>
Public Class clsProject
    Implements IDisposable

#Region "Shared Variables"

    ''' <summary>
    ''' Number of seconds before connection timeout error occurs (used when call TestConnectionQuick in clsDBBase)
    ''' </summary>
    Friend Const CONNECTIONTIMEOUT As Integer = 10

    ''' <summary>
    ''' Name of temporary database used in some data providers to better segregate temporary tables from rest of tables (so can make project database readonly and still change temp tables)
    ''' </summary>
    Public Shared TempDBName As String = "TempDB"

    ' ''' <summary>
    ' ''' Name of directory for temporary files (does NOT end with backslash)
    ' ''' </summary>
    'Public Shared TempPath As String = String.Format("{0}\WRDB", My.Computer.FileSystem.SpecialDirectories.Temp)

    Private Shared _TempPath As String = "", _TempPrefix As String = "", _DataPrefix As String = ""

    ''' <summary>
    ''' Name of directory for temporary files (does NOT end with backslash)
    ''' </summary>
    ''' <remarks>ASP.Net does not allow access to SpecialDirectories.Temp; therefore, before doing anything, ASP.Net caller must set using "Server.MapPath("~") and permissions must be set to allow WRDB folder to be created</remarks>
    Public Shared Property TempPath As String
        Get
            Dim WRDBTempPath As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Temp, "WRDB")
            If Not My.Computer.FileSystem.DirectoryExists(WRDBTempPath) Then My.Computer.FileSystem.CreateDirectory(WRDBTempPath)
            If _TempPath = "" Then _TempPath = IO.Path.Combine(WRDBTempPath, Guid.NewGuid.ToString)
            If Not My.Computer.FileSystem.DirectoryExists(_TempPath) Then My.Computer.FileSystem.CreateDirectory(_TempPath)
            Return _TempPath
        End Get
        Set(ByVal value As String)
            _TempPath = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for temporary table names that uniquely identifies the user and session
    ''' </summary>
    ''' <remarks>ASP.Net does not track by loginname; instead, must path unique identifier based on session</remarks>
    Public Shared Property TempPrefix As String
        Get
            If _TempPrefix = "" Then _TempPrefix = "TEMP_"
            Return _TempPrefix
        End Get
        Set(ByVal value As String)
            _TempPrefix = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for temporary data tables (opened using Open Data File) that uniquely identifies the user and session
    ''' </summary>
    ''' <remarks>ASP.Net does not track by loginname; instead, must path unique identifier based on session</remarks>
    Public Shared Property DataPrefix As String
        Get
            If _DataPrefix = "" Then _DataPrefix = "DATA_"
            Return _DataPrefix
        End Get
        Set(ByVal value As String)
            _DataPrefix = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for permanent non-WRDB data tables that must be retained but that you don't want to appear as a Working table
    ''' (for example, ARC application security and forecast tables)
    ''' </summary>
    Public Shared ReadOnly Property OtherPrefix As String
        Get
            Return "OTHR_"
        End Get
    End Property

    ''' <summary>
    ''' Name of table or view containing previous query results; if using neither, will contain subquery SELECT statement
    ''' </summary>
    Public Shared TempData As String

    ''' <summary>
    ''' Name of view containing subquery results for complex queries
    ''' </summary>
    Public Shared TempView As String

    ''' <summary>
    ''' Name of temporary table containing report results
    ''' </summary>
    Public Shared RptData As String

    ''' <summary>
    ''' Name of temporary table containing data acquired from external source
    ''' </summary>
    Public Shared ExtData As String

    Public SupportTableStructuresChecked As Boolean = False

    Public dictReports As New Generic.Dictionary(Of String, String)

    Public Shared ReadOnly Property LocalWRDBPath As String
        Get
            Dim s As String = String.Format("{0}\WRDB", My.Computer.FileSystem.SpecialDirectories.MyDocuments)
            If Not My.Computer.FileSystem.DirectoryExists(s) Then My.Computer.FileSystem.CreateDirectory(s)
            Return s
        End Get
    End Property

    ''' <summary>
    ''' Return full path to help file (copies to local computer if necessary to avoid problem with .chm not working across network)
    ''' </summary>
    ''' <param name="HelpFile">Name of help file (e.g., "WRDB.chm")</param>
    Public Shared ReadOnly Property HelpFilePath(HelpFile As String) As String
        Get
            Dim AppHelpDir As String = IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location) & "\Help"
            If IsNetworkResource(AppHelpDir) Then
                'see if help file has already been copied
                Dim LocalHelpPath As String = String.Format("{0}\HELP", LocalWRDBPath)
                If Not My.Computer.FileSystem.DirectoryExists(LocalHelpPath) Then My.Computer.FileSystem.CreateDirectory(LocalHelpPath)

                If My.Computer.FileSystem.FileExists(LocalHelpPath & "\" & HelpFile) Then Return LocalHelpPath & "\" & HelpFile

                If My.Computer.FileSystem.FileExists(AppHelpDir & "\" & HelpFile) Then
                    My.Computer.FileSystem.CopyFile(AppHelpDir & "\" & HelpFile, LocalHelpPath & "\" & HelpFile, False)
                    Return LocalHelpPath & "\" & HelpFile
                Else
                    Throw New IO.FileNotFoundException(String.Format("Unable to find help file named '{0}'; please restore this file or reinstall application.", HelpFile.Replace("\", "\\")))
                    Return ""
                End If
            Else
                Return AppHelpDir & "\" & HelpFile
            End If
        End Get
    End Property

    ''' <summary>
    ''' Given path, determine if is referencing a network or local resource
    ''' </summary>
    ''' <param name="DirectoryName">Directory name</param>
    ''' <returns>True if references a network resource</returns>
    Public Shared Function IsNetworkResource(DirectoryName As String) As Boolean
        If DirectoryName.StartsWith("\\") Then Return True
        Dim di As New System.IO.DirectoryInfo(DirectoryName)
        If IO.Path.IsPathRooted(di.FullName) Then
            Dim dr As New System.IO.DriveInfo(IO.Path.GetPathRoot(di.FullName))
            If dr.DriveType = IO.DriveType.Network Then Return True
        End If
        Return False
    End Function


#End Region

#Region "Private Variables"

    Public DataSet As New DataSet

    ''' <summary>
    ''' Dataset which holds all table structures; used to create new tables; table names are same as support tables or "Working" 
    ''' </summary>
    ''' <remarks></remarks>
    Public dsWRDB As New DataSet

    Private StartQuery As Date
    Private QuerySQL As String 'so completed event has access to SQL string
    Public IsQueryBusy As Boolean

    Private _IsLoggedIn As Boolean = False

    Public Property IsLoggedIn As Boolean
        Get
            Return _IsLoggedIn OrElse Password.Trim = ""
        End Get
        Set(ByVal value As Boolean)
            _IsLoggedIn = value
        End Set
    End Property

    ''' <summary>
    ''' If false, selected advanced menu and toolbar items will be hidden for non-Supervisor users
    ''' </summary>
    Public Property AdvancedMenusVisible As Boolean = True

    ''' <summary>
    ''' If true, non-Supervisors will have no ability to alter any Working tables in addition to other restrictions
    ''' </summary>
    Public Property IsReadOnly As Boolean = False

    ''' <summary>
    ''' If true, WQX fields and Support tables will be visible in the Support table browser
    ''' </summary>
    Public Property WQXVisible As Boolean = False

    Public WithEvents DB As clsDB

    ''' <summary>
    ''' Is raised during long operations (e.g., import/export) so caller can report using UI
    ''' </summary>
    ''' <param name="Value">Current value (must alway initialize with 0 and finalize with Value=MaxValue)</param>
    ''' <param name="MaxValue">Maximum value</param>
    ''' <param name="ProgressText">Text to display</param>
    Public Event Progress(ByRef DB As clsDB, Value As Long, MaxValue As Long, ProgressText As String)

#End Region

    ''' <summary>
    ''' List of standard WRDB tables associated with a project
    ''' </summary>
    Public Enum enumTableTypes As Integer
        Temporary 'data selected by query
        Working  'may have many working tables
        Master   'single master table
        Stations 'following are support tables
        PCodes
        CCodes
        RCodes
        QCodes
        Branches
        StaGrp
        PCodeGrp
        ConvGrp
        Agencies 'validation table
        FIPSCodes 'validation table (will be automatically populated for entire U.S.)
        WQXMethods 'validation table (will be automatically populated with WQX items)
        Validation 'validation for station types, basin names, waterbodies, ecoregions, planregions, wqx characteristic names, sample fractions, and measure units (will be auto-populated for WQX items)
        Criteria  'criteria override table in which criteria limits in PCodes table can be overridden by a field in Stations table (used for advanced criteria analysis)
        Journal   'tracks multiple operations per Working table (added in WRDB 6.0)
        Tracking  'generally one record per Working table
        Settings 'program-wide Project settings and preferences will be stored here instead of in registry
        MiniData 'special data table type containing only Station_ID, PCode, Date_Time, and Result used to create WASP input files
        Legacy   'WRDB 4.5 schema for working tables (Paradox exports use this)
        HiddenData 'working table schema; these tables are created by WRDB 6 and reused to hold external data, etc.
        Other    'non-WRDB data tables that you want to hide so they are not opened as Working tables
    End Enum

    Public Enum enumDisplayRecordCount
        Never
        Always
        Queries_Only
    End Enum

    Public Enum enumDesktopColorTheme
        None
        Water
        Desert
        Forest
    End Enum

    Public Enum enumDesktopGraphicOption
        Centered
        Scaled
        Stretched
    End Enum

    Public Enum enumQueryOption
        View
        Table
        Subselect
    End Enum

    Public Enum enumReportScaling
        None
        Stretched
        Multiline
        ScaledFonts
    End Enum

    Public Enum enumReportLanguage
        English
        Spanish
    End Enum

    Public Enum enumGisOutputOption
        Points
        Grid
        Both
    End Enum

    'Public Enum enumReportCompatibility
    '    SQL_Server_2005
    '    SQL_Server_2008
    'End Enum

    ''' <summary>
    ''' Array of standard table names for each table type
    ''' </summary>
    Public _TableName() As String = {"Temporary", "Working", "Master", "Stations", "PCodes", "CCodes", "RCodes", "QCodes", "Branches", "StaGrp", "PCodeGrp", "ConvGrp", "Agencies", "FIPSCodes", "WQXMethods", "Validation", "Criteria", "Journal", "Tracking", "Settings", "MiniData", "Legacy", "Othr_", "Data_"}

#Region "Properties"

    Public Property Name() As String

    Public Property Description() As String

    Public Property Password As String

    Public Property TitlebarText() As String

    Public Property DesktopGraphicFile() As String

    Public Property DesktopGraphicOption As enumDesktopGraphicOption = enumDesktopGraphicOption.Centered

    Public Property DesktopColorTheme() As enumDesktopColorTheme = enumDesktopColorTheme.None

    Public Property MasterTableName() As String

    Public Property WorkingDir() As String

    Public Property MasterDir() As String

    Public Property SupportDir() As String

    Public Property SupportDirProject() As String

    Public Property SQLDir As String

    Public Property SelectAvailable As Boolean

    Public Property ReportHeading1 As String

    Public Property ReportHeading2 As String

    Public Property ReportScaling As enumReportScaling

    Public Property ReportLanguage As enumReportLanguage

    'Public Property ReportCompatibility As enumReportCompatibility

    'this was removed 8/27/10 when last record counts placed in Tracking table
    'put back in 3/10/16 for huge databases (EGAD)
    Public Property DisplayRecordCount As enumDisplayRecordCount = enumDisplayRecordCount.Always

    Public Property StoreObjects() As Boolean

    Private _QueryOption As enumQueryOption

    Public Property QueryOption() As enumQueryOption
        Get
            If (Not HasTablePermissions And _QueryOption = enumQueryOption.Table) OrElse (DB IsNot Nothing AndAlso Not DB.SupportsViews AndAlso _QueryOption = enumQueryOption.View) Then Return enumQueryOption.Table Else Return _QueryOption
        End Get
        Set(ByVal value As enumQueryOption)
            _QueryOption = value
        End Set
    End Property

    ''' <summary>If true then data tables will use larger field sizes for PCode, CCode, etc.; this was added to support EGAD views mapped to longer fields</summary>
    Public Property ExtendedFieldSize As Boolean

    Public Property GisQuickList() As Boolean

    Public Property GisQuickTable() As Boolean

    Public Property GisDirectory() As String

    Public Property GisBasemapStations As String

    Public Property GisBasemapOutput As String

    ''' <summary>Filename of polygon layer automatically used for clipping</summary>
    Public Property GisClippingLayerFilename As String = ""

    Public Property GisOutputOption As enumGisOutputOption = enumGisOutputOption.Points

    ''' <summary>
    ''' The number of grid rows/columns across the clipping or source layer
    ''' </summary>
    Public Property GisOutputGridSize As Integer = 100

    ''' <summary>
    ''' The number of nearby stations to include in spatial interpolation
    ''' </summary>
    Public Property GisNumPoints As Integer = 5

    ''' <summary>Name of default criteria regulatory group</summary>
    Public Property CriteriaRegGroup As String

    ''' <summary>This is a comma-separated list of users allowed for this project; if blank then there are no restrictions</summary>
    ''' <remarks>This doesn't affect security (use database permissions for that) because user can change his initials to appear to be someone else--just a convenience</remarks>
    Public Property AllowedUsers As String = ""

    Public Property IncludeInBackup As Boolean = False

    ''' <summary>If true, will examine result field, if RCode is null and result is less than PCode detection limit, will automatically assume is a BDL value with best guess numeric value shown in the result field (that is, it ignores the multiplier)</summary>
    Public Property AutoBDL As Boolean = False

#End Region

    ''' <summary>
    ''' Alternative way of initializing because can't include "New" entries in interface used by EPDRiv1
    ''' </summary>
    Public Sub New()
        Initialize("")
    End Sub

    ''' <summary>
    ''' Get preferences and setup info from INI file for the project (or assign defaults)
    ''' </summary>
    ''' <param name="ProjectName">Name of project from WRDB.INI file</param>
    Public Sub New(ByVal ProjectName As String, Optional ByVal IsTempProject As Boolean = False)
        Initialize(ProjectName, IsTempProject)
    End Sub

    ''' <summary>
    ''' Alternative way of initializing because can't include "New" entries in interface used by EPDRiv1
    ''' </summary>
    ''' <param name="ProjectName">Name of project from WRDB.INI file</param>
    ''' <param name="IsTempProject">If true, is a temporary project which should not already exist in INI file (bypass reading which is slow)</param>
    ''' <remarks>This also establishes all standard table structures and stored them in empty datatables within a global dataset; it allows tables to be created in provider-independent manner</remarks>
    <System.Diagnostics.DebuggerStepThrough()>
    Public Sub Initialize(ByVal ProjectName As String, Optional ByVal IsTempProject As Boolean = False)

        If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)

        Me.Name = ProjectName

        CreateTableTemplates()

        ' Configuration file in "ini" format containing general and project-specific settings (note: references current global Project variable)
        ' Ini file used for all program settings (except form size and position) instead of My.Settings because latter cannot handle variable number of projects
        ' The IniFile variable is instantiated when clsWRDB is initialized; the file is WRDB.ini and is stored in AppDataPath folder
        ' (e.g., C:\Documents and Settings\LCW\My Documents\WRDB\WRDB.INI)

        If Not IsTempProject Then

            clsConnections.GetProjectInfo(Me) 'this will instantiate DB
            If DB Is Nothing OrElse Not DB.TestConnectionQuick(CONNECTIONTIMEOUT) Then Exit Sub

            With New clsProjectSettings(Me)
                .GetProjectInfo()
            End With

        End If

        If DB IsNot Nothing AndAlso DB.Connection IsNot Nothing Then
            TempPrefix = Me.DB.GetValidTableName(TempPrefix)
            DataPrefix = Me.DB.GetValidTableName(DataPrefix)
            HasTablePermissions = DB.HasTablePermissions
        End If

        If False And Debugger.IsAttached Then
            CreateAllViewScripts()
            CreateAllTableScripts()
            Debug.Assert(False, "Scripts created.")
        End If
    End Sub

    Public Sub CreateTableTemplates()

        'paradox temporary tables are not indexed (so can write to them using modParadox), but working and master are
        'RecID is sequential number; cannot use autoincrement because not all providers allow it; handle manually
        'WRDB 4.5 gives an error message if you open this table (it wants to see a primary key field called INDEX--what a horrible choice)
        'To allow backwards compatibility, distribute minor fix to WRDBLib that also allows RecID

        Dim dt As DataTable

        dsWRDB.Tables.Clear()

        With dsWRDB.Tables.Add("Temporary")
            With .Columns
                'starting with 6.0.0.4 all data tables will have RecID field
                'NOTE: field sizes are also specified in clsImport class
                .Add(NewColumn("RecID", "RecID", GetType(Integer)))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), IIf(ExtendedFieldSize, 60, 25)))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("PCode", "PCode", GetType(String), IIf(ExtendedFieldSize, 25, 10)))
                .Add(NewColumn("LEW_Pct", "LEW", GetType(Double)))
                .Add(NewColumn("Depth_M", "Depth", GetType(Double)))
                .Add(NewColumn("Agency", "Acy", GetType(String), 5))
                .Add(NewColumn("CCode", "CCode", GetType(String), IIf(ExtendedFieldSize, 15, 3)))
                .Add(NewColumn("LCode", "L", GetType(String), IIf(ExtendedFieldSize, 5, 3)))
                .Add(NewColumn("SCode", "S", GetType(String), IIf(ExtendedFieldSize, 5, 3)))
                .Add(NewColumn("QCode", "Q", GetType(String), IIf(ExtendedFieldSize, 5, 1)))
                .Add(NewColumn("RCode", "R", GetType(String), IIf(ExtendedFieldSize, 5, 1)))
                .Add(NewColumn("Result", "Result", GetType(Double)))
                .Add(NewColumn("Validated", "Val", GetType(Boolean), , False))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
                .Add(NewColumn("Owner", "Owner", GetType(String), 3))
                .Add(NewColumn("Track_ID", "Trk ID", GetType(Integer)))
                If _StoreObjects Then
                    .Add(NewColumn("Object_Type", "Attach", GetType(String), 5)) 'string describing type of data stored (often file extension)
                    .Add(NewColumn("Object_Value", "Item Val", GetType(Byte()))) 'binary data
                End If
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}

            dt = .Copy
            dt.TableName = "Working"
            dsWRDB.Tables.Add(dt)
            dt = dt.Copy
            dt.TableName = "Master"
            dsWRDB.Tables.Add(dt)

            'because they are shared, temporary tables need one more field to identify who selected the records
            .Columns.Add(NewColumn("Selected_By", "Sel By", GetType(String), 3))
            .PrimaryKey = New DataColumn() { .Columns("Selected_By"), .Columns("RecID")}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Stations))
            With .Columns
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), IIf(ExtendedFieldSize, 60, 25)))
                .Add(NewColumn("Station_Name", "Station Name", GetType(String), 60))
                .Add(NewColumn("Station_Type", "Station Type", GetType(String), 60))
                .Add(NewColumn("Data_Freq", "Data Freq", GetType(String), 8))
                .Add(NewColumn("Latitude", "Latitude", GetType(Double)))
                .Add(NewColumn("Longitude", "Longitude", GetType(Double)))
                .Add(NewColumn("Elevation", "Elevation", GetType(Double)))
                .Add(NewColumn("HUC", "HUC", GetType(String), 14))
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("River_Mile", "River Mile", GetType(Double)))
                .Add(NewColumn("Reach_ID", "Reach ID", GetType(String), 25))
                .Add(NewColumn("Dr_Area", "Dr Area", GetType(Double)))
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Ref_ID", "Reference ID", GetType(String), 20))
                .Add(NewColumn("Remarks", "Remarks", GetType(String), 50))
                .Add(NewColumn("Other", "Other", GetType(String), 50))
                .Add(NewColumn("WaterBodyType", "Water Body Type", GetType(String), 30))
                .Add(NewColumn("Basin_Name", "Basin Name", GetType(String), 30))
                .Add(NewColumn("State", "State", GetType(String), 2))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("FIPS_Code", "FIPS Code", GetType(Integer)))
                .Add(NewColumn("EcoRegion", "Eco Region", GetType(String), 30))
                .Add(NewColumn("PlanRegion", "Plan Region", GetType(String), 30))
                .Add(NewColumn("Convert_Station_ID", "Convert To", GetType(String), 25))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.PCodes))
            With .Columns
                .Add(NewColumn("PCode", "PCode", GetType(String), IIf(ExtendedFieldSize, 25, 10)))
                .Add(NewColumn("Anal_Name", "Analysis Name", GetType(String), 60))
                .Add(NewColumn("Units", "Units", GetType(String), 60))
                .Add(NewColumn("Test_Method", "Test Method", GetType(String), 12))
                .Add(NewColumn("Det_Limit", "Det. Limit", GetType(Double), , 0))
                .Add(NewColumn("Sig_Figs", "Sig Figs", GetType(Double), , 2))
                .Add(NewColumn("Min_Limit", "Min. Limit", GetType(Double)))
                .Add(NewColumn("Max_Limit", "Max. Limit", GetType(Double)))
                .Add(NewColumn("Min_Criteria", "Min. Crit.", GetType(Double)))
                .Add(NewColumn("Max_Criteria", "Max. Crit.", GetType(Double)))
                .Add(NewColumn("Format_Str", "Fmt Str", GetType(String), 10, "w.2"))
                .Add(NewColumn("Convert_PCode", "Convert To", GetType(String), 10))
                .Add(NewColumn("Convert_Units", "Units", GetType(String), 12, "mg/L"))
                .Add(NewColumn("Convert_Mult", "Multiplier", GetType(Double), , 1.0))
                .Add(NewColumn("WQX_CharacteristicName", "WQX Characteristic Name", GetType(String), 120))
                .Add(NewColumn("WQX_ResultSampFraction", "WQX Sample Fraction", GetType(String), 25))
                .Add(NewColumn("WQX_ResultMeasureUnit", "WQX Units", GetType(String), 12))
                .Add(NewColumn("WQX_AnalyticalMethodIDContext", "WQX Method ID Context", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodID", "WQX Method ID", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodName", "WQX Method Name", GetType(String), 120))
                .Add(NewColumn("WQX_AnalyticalMethodQualifType", "WQX Method Type", GetType(String), 25))
                .Add(NewColumn("WQX_AnalyticalMethodDesc", "WQX Method Desc", GetType(String), 500))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.CCodes))
            With .Columns
                .Add(NewColumn("CCode", "CCode", GetType(String), IIf(ExtendedFieldSize, 15, 3)))
                .Add(NewColumn("CCode_Name", "Name", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 30))
                .Add(NewColumn("Convert_CCode", "Convert To", GetType(String), 3))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.RCodes))
            With .Columns
                .Add(NewColumn("RCode", "RCode", GetType(String), IIf(ExtendedFieldSize, 5, 1)))
                .Add(NewColumn("Description", "Description", GetType(String), 250))
                .Add(NewColumn("Convert_RCode", "Convert To", GetType(String), 1))
                .Add(NewColumn("Calc_Stats", "Calc Stats?", GetType(Boolean), , True))
                .Add(NewColumn("NonDetect_PCode", "Is Non-Detect (Use PCode Limit)", GetType(Boolean), , True))
                .Add(NewColumn("NonDetect_Result", "Is Non-Detect (Use Result Limit)", GetType(Boolean), , False))
                .Add(NewColumn("Detect_Mult", "Limit Multiplier", GetType(Double), , 0.5))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.QCodes))
            With .Columns
                .Add(NewColumn("QCode", "QCode", GetType(String), IIf(ExtendedFieldSize, 5, 1)))
                .Add(NewColumn("QCode_Name", "Name", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 30))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Branches))
            With .Columns
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("Branch_Name", "Name", GetType(String), 60))
                .Add(NewColumn("DS_Branch_ID", "DS Branch ID", GetType(String), 16))
                .Add(NewColumn("DS_Branch_RM", "DS RMile", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.StaGrp))
            With .Columns
                .Add(NewColumn("StationGroup", "Group Name", GetType(String), 100))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), IIf(ExtendedFieldSize, 60, 25)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.PCodeGrp))
            With .Columns
                .Add(NewColumn("PCodeGroup", "Group Name", GetType(String), 100))
                .Add(NewColumn("PCode", "PCode", GetType(String), IIf(ExtendedFieldSize, 25, 10)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.ConvGrp))
            With .Columns
                .Add(NewColumn("ConvGroup", "Group Name", GetType(String), 15))
                .Add(NewColumn("ConvFrom", "Convert From", GetType(String), 250))
                .Add(NewColumn("ConvTo", "Convert To", GetType(String), 250))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1), .Columns(2)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Agencies))
            With .Columns
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Agency_Name", "Name", GetType(String), 100))
                .Add(NewColumn("Description", "Description", GetType(String), 100))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.FIPSCodes))
            With .Columns
                .Add(NewColumn("FIPS_Code", "FIPS Code", GetType(Integer)))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("State", "State", GetType(String), 2))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.WQXMethods))
            With .Columns
                .Add(NewColumn("WQX_AnalyticalMethodIDContext", "WQX Method ID Context", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodID", "WQX Method ID", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodName", "WQX Method Name", GetType(String), 120))
                .Add(NewColumn("WQX_AnalyticalMethodQualifType", "WQX Method Type", GetType(String), 25))
                .Add(NewColumn("WQX_AnalyticalMethodDesc", "WQX Method Desc", GetType(String), 500))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Validation))
            With .Columns
                .Add(NewColumn("Field_Name", "Field Name", GetType(String), 40))
                .Add(NewColumn("Valid_Value", "Valid Value", GetType(String), 120))
                .Add(NewColumn("Description", "Description", GetType(String), 120))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Criteria))
            With .Columns
                .Add(NewColumn("RegGroup", "Regulatory Group", GetType(String), 15))
                .Add(NewColumn("CritGroup", "Criteria Group", GetType(String), 15))
                .Add(NewColumn("PCode", "PCode", GetType(String), IIf(ExtendedFieldSize, 25, 10)))
                .Add(NewColumn("Month_List", "Month List", GetType(String), 50))
                .Add(NewColumn("Priority", "Priority Mult", GetType(Double)))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), IIf(ExtendedFieldSize, 60, 25)))
                .Add(NewColumn("Station_Type", "Station Type", GetType(String), 60))
                .Add(NewColumn("HUC", "HUC", GetType(String), 14))
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("Reach_ID", "Reach ID", GetType(String), 25))
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Remarks", "Remarks", GetType(String), 50))
                .Add(NewColumn("Other", "Other", GetType(String), 50))
                .Add(NewColumn("WaterBodyType", "Water Body Type", GetType(String), 30))
                .Add(NewColumn("Basin_Name", "Basin Name", GetType(String), 30))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("EcoRegion", "Eco Region", GetType(String), 30))
                .Add(NewColumn("PlanRegion", "Plan Region", GetType(String), 30))
                .Add(NewColumn("Min_Criteria", "Min. Crit.", GetType(Double)))
                .Add(NewColumn("Max_Criteria", "Max. Crit.", GetType(Double)))
                .Add(NewColumn("Violation_Type", "Violation Type", GetType(String), 10))
                .Add(NewColumn("Violation_Freq", "Frequency", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1), .Columns(2), .Columns(3)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Journal))
            With .Columns
                .Add(NewColumn("Table_Name", "Table Name", GetType(String), 100))
                .Add(NewColumn("Time_Stamp", "Time Stamp", GetType(Date)))
                .Add(NewColumn("User_Init", "User", GetType(String), 3))
                .Add(NewColumn("Entry_Type", "Entry Type", GetType(String), 10))
                .Add(NewColumn("Description", "Description", GetType(String), 255))
                .Add(NewColumn("Source", "Data Source", GetType(String), 255))
                .Add(NewColumn("Record_Count", "Record Count", GetType(Integer)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Tracking))
            With .Columns
                .Add(NewColumn("Temp_DB_Name", "Working Table", GetType(String), 100))
                .Add(NewColumn("Master_DB_Name", "Master Table", GetType(String), 100))
                .Add(NewColumn("TableGroup", "Table Group", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 100))
                .Add(NewColumn("Source", "Data Source", GetType(String), 100))
                .Add(NewColumn("QCode", "QCode", GetType(String), IIf(ExtendedFieldSize, 5, 1)))
                .Add(NewColumn("Q_Info", "Quality Information", GetType(String), 100))
                .Add(NewColumn("Record_Count", "Record Count", GetType(Integer)))
                .Add(NewColumn("Create_Update", "Last Updated", GetType(Date)))
                .Add(NewColumn("Track_ID", "Tracking ID", GetType(Integer)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1), .Columns(2)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Settings))
            With .Columns
                .Add(NewColumn("SectionName", "Section", GetType(String), 255))
                .Add(NewColumn("KeyName", "Key", GetType(String), 255))
                .Add(NewColumn("Setting", "Value", GetType(String), 255))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.MiniData))
            With .Columns
                .Add(NewColumn("RecID", "RecID", GetType(Integer)))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("Result", "Result", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() { .Columns(0)}
        End With

        'compatible with WRDB 4.5 for Paradox
        With dsWRDB.Tables.Add(TableName(enumTableTypes.Legacy))
            With .Columns
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
                .Add(NewColumn("LEW_Pct", "LEW", GetType(Double)))
                .Add(NewColumn("Depth_M", "Depth", GetType(Double)))
                .Add(NewColumn("CCode", "CCode", GetType(String), 3))
                .Add(NewColumn("RCode", "R", GetType(String), 1))
                .Add(NewColumn("Result", "Result", GetType(Double)))
                .Add(NewColumn("Validated", "Val", GetType(Boolean), , False))
                .Add(NewColumn("Track_ID", "Trk ID", GetType(Integer)))
            End With
        End With

        dt = dsWRDB.Tables(enumTableTypes.Working).Copy
        dt.TableName = TableName(enumTableTypes.HiddenData)
        dsWRDB.Tables.Add(dt)

    End Sub

    ''' <summary>
    ''' Make deep copy of project
    ''' </summary>
    ''' <returns>New project</returns>
    Public Function Clone() As clsProject
        Dim NewProject As New clsProject(Name)
        With NewProject
            .Description = Description
            .DesktopGraphicFile = DesktopGraphicFile
            .DesktopGraphicOption = DesktopGraphicOption
            .DesktopColorTheme = DesktopColorTheme
            .MasterDir = MasterDir
            .MasterTableName = MasterTableName
            .SupportDir = SupportDir
            .SupportDirProject = SupportDirProject
            .TitlebarText = TitlebarText
            .WorkingDir = WorkingDir
            .IsLoggedIn = IsLoggedIn
            .Password = Password
            .AdvancedMenusVisible = AdvancedMenusVisible
            If DB IsNot Nothing Then .DB = DB.Clone
        End With
        Return NewProject
    End Function

    ''' <summary>
    ''' Set column caption headings and maximum column width using standard templates; usually called after AddTable
    ''' </summary>
    Public Sub SetTableHeadings(ByVal TableType As enumTableTypes)
        Dim ErrCol As String = ""
        Dim tbl As DataTable = DataSet.Tables(TableName(TableType))
        With GetTableTemplate(TableType)
            For Each col As DataColumn In .Columns
                For i As Integer = 0 To tbl.Columns.Count - 1
                    If tbl.Columns(i).ColumnName.Equals(col.ColumnName.ToUpper, StringComparison.InvariantCultureIgnoreCase) Then
                        tbl.Columns(i).Caption = col.Caption
                        If tbl.Columns(i).DataType Is GetType(String) And Not ExtendedFieldSize Then
                            Try
                                tbl.Columns(i).MaxLength = col.MaxLength
                            Catch ex As Exception
                                If DB.DatabaseType <> clsDB.enumDBType.Oracle Then ErrCol &= String.Format("\n{0} ({1})", col.Caption, col.MaxLength)
                            End Try
                        End If
                        Exit For
                    End If
                Next
            Next
            If ErrCol <> "" Then Throw New ApplicationException(String.Format("One or more entries in the following columns of the table '{0}' were had more than the maximum allowable number of characters\n{1}\n\nPlease edit the data to conform to the maximum limits.", .TableName, ErrCol))
        End With
    End Sub

    ''' <summary>
    ''' Set column caption headings and maximum column width using standard templates; usually called after AddTable
    ''' </summary>
    Public Sub SetTableHeadings(TableName As String)
        Dim ErrCol As String = ""
        Dim tbl As DataTable = DataSet.Tables(TableName)
        With GetTableTemplate(GetTableType(TableName))
            For Each col As DataColumn In .Columns
                For i As Integer = 0 To tbl.Columns.Count - 1
                    If tbl.Columns(i).ColumnName.Contains(col.ColumnName.ToUpper) Or tbl.Columns(i).ColumnName.Contains(col.ColumnName) Then
                        tbl.Columns(i).Caption = col.Caption
                        If tbl.Columns(i).DataType Is GetType(String) Then
                            Try
                                tbl.Columns(i).MaxLength = col.MaxLength
                            Catch ex As Exception
                                If DB.DatabaseType <> clsDB.enumDBType.Oracle Then ErrCol &= String.Format("\n{0} ({1})", col.Caption, col.MaxLength)
                            End Try
                        End If
                        Exit For
                    End If
                Next
            Next
            If ErrCol <> "" Then Throw New ApplicationException(String.Format("One or more entries in the following columns of the table '{0}' were had more than the maximum allowable number of characters\n{1}\n\nPlease edit the data to conform to the maximum limits.", .TableName, ErrCol))
        End With
    End Sub

    ''' <summary>
    ''' Set column caption headings and maximum column width using standard templates; usually called after AddTable (datatable must represent Working or Master)
    ''' </summary>
    Public Sub SetTableHeadings(ByRef tbl As DataTable)
        Dim ErrCol As String = ""
        With GetTableTemplate(enumTableTypes.Working)
            For Each col As DataColumn In .Columns
                For i As Integer = 0 To tbl.Columns.Count - 1
                    If tbl.Columns(i).ColumnName.Contains(col.ColumnName.ToUpper) Or tbl.Columns(i).ColumnName.Contains(col.ColumnName) Then
                        tbl.Columns(i).Caption = col.Caption
                        If tbl.Columns(i).DataType Is GetType(String) Then
                            Try
                                tbl.Columns(i).MaxLength = col.MaxLength
                            Catch ex As Exception
                                If DB.DatabaseType <> clsDB.enumDBType.Oracle Then ErrCol &= String.Format("\n{0} ({1})", col.Caption, col.MaxLength)
                            End Try
                        End If
                        Exit For
                    End If
                Next
            Next
            If ErrCol <> "" Then Throw New ApplicationException(String.Format("One or more entries in the following columns of the table '{0}' were had more than the maximum allowable number of characters\n{1}\n\nPlease edit the data to conform to the maximum limits.", .TableName, ErrCol))
        End With
    End Sub

    ''' <summary>
    ''' Save all preferences to this project's INI file
    ''' </summary>
    Public Sub SavePreferences()
        clsConnections.SetProjectInfo(Me)
        With New clsProjectSettings(Me)
            .SetProjectInfo()
        End With
    End Sub

    ''' <summary>
    ''' If path contains {MyDocs}, replace with full path; if starts with My Documents path, replace with {MyDocs}
    ''' </summary>
    ''' <param name="Path">Path to convert from</param>
    ''' <returns>Path with substitution made</returns>
    ''' <remarks></remarks>
    Shared Function ReplaceMyDocs(ByVal Path As String) As String
        Dim MyDocuments As String = Nothing
        Try
            MyDocuments = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        Catch ex As IO.DirectoryNotFoundException
            'this will occur when running on web server; assume no need for substitution
            Return Path
        End Try

        If String.IsNullOrEmpty(Path) Then Return ""
        If Path.StartsWith("{MyDocs}", StringComparison.OrdinalIgnoreCase) Then
            Return Path.Replace("{MyDocs}", MyDocuments)
        ElseIf Path.StartsWith(MyDocuments, StringComparison.OrdinalIgnoreCase) Then
            Return Path.Replace(MyDocuments, "{MyDocs}")
        Else
            Return Path
        End If
    End Function

    ''' <summary>
    ''' Helper method to fill combo- or listbox controls with a list of keyfields, hyphen, description (key - descr)
    ''' List will be sorted and set to first value in the list (or default value is it exists)
    ''' To retrieve the keyfield value, use GetPickList function
    ''' (This version is intended for use with Validation Support table)
    ''' </summary>
    ''' <param name="List_ComboBox">Listbox or combobox object, or Generic list of string</param>
    ''' <param name="ValidationKey">Name of validation key (e.g., "SCODE")</param>
    ''' <param name="AdditionalItem">Optional additional list item (e.g., "(Select from list)"</param>
    ''' <param name="DefaultKey">Default key value, if exists in list (if Nothing, will be set to first item in list)</param>
    Public Sub FillPickListValidation(ByRef List_ComboBox As Object, ByVal ValidationKey As String, Optional ByVal AdditionalItem As String = "", Optional ByVal DefaultKey As String = Nothing)

        Dim dr As DbDataReader = Nothing

        Try
            Dim DefaultText As String = ""
            Dim List As New Generic.List(Of String)

            With List
                .Clear()
                If AdditionalItem <> "" Then .Add(AdditionalItem)
                dr = DB.ExecuteReader("SELECT DISTINCT Valid_Value,Description FROM {0} WHERE Field_Name='{1}' ORDER BY Valid_Value", FullTableName(enumTableTypes.Validation), ValidationKey.ToUpper)
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then
                        .Add(dr.GetString(0) & " - " & TestNull(dr.GetValue(1), ""))
                    End If
                End While
                dr.Close()
            End With

            If TypeOf List_ComboBox Is Generic.List(Of String) Then
                List_ComboBox = List
            Else 'is list or combobox
                With List_ComboBox
                    .items.clear()
                    .items.addrange(List.ToArray)
                    If DefaultKey Is Nothing Then 'default not requested; set to first item
                        .SelectedIndex = IIf(.Items.Count = 0, -1, 0)
                    Else
                        If DefaultText = "" Then 'default requested but couldn't be found; add to list
                            .items.add(DefaultKey)
                            .text = DefaultKey
                        Else
                            .Text = DefaultText
                        End If
                    End If
                End With
            End If
        Catch ex As Exception
            Throw
        Finally
            If dr IsNot Nothing Then dr.Close()
            DB.CloseConnection()
        End Try
    End Sub

    ''' <summary>
    ''' Helper method to fill combo- or listbox controls with a list of keyfields, hyphen, description (key - descr);
    ''' List will be sorted and set to first value in the list (or default value is it exists);
    ''' To retrieve the keyfield value, use GetPickList function;
    ''' </summary>
    ''' <param name="List_ComboBox">Listbox or combobox object, or Generic list of string</param>
    ''' <param name="DataTablename">Name of data table to query unique keys from; if only querying all records in Support table, leave empty ("")</param>
    ''' <param name="SupportTableName">Name of Support table with keys and descriptions</param>
    ''' <param name="KeyFieldName">Name of key field in datatable and support table</param>
    ''' <param name="DescriptionFieldName">Name of description field in support table (if empty, will ignore)</param>
    ''' <param name="AdditionalItem">Optional additional list item (e.g., "(Select from list)"</param>
    ''' <param name="DefaultKey">Default key value; if doesn't exist in list will add (if blank, will be set to first item in list)</param>
    ''' <param name="WhereClause">SQL WHERE clause (including 'WHERE' keyword) for selecting records from datatable</param>
    Public Overridable Sub FillPickList(ByRef List_ComboBox As Object,
                                        DataTablename As String, SupportTableName As String,
                                        KeyFieldName As String, DescriptionFieldName As String,
                                        Optional AdditionalItem As String = "",
                                        Optional DefaultKey As String = Nothing,
                                        Optional WhereClause As String = "")

        Const MAXLISTSIZE = 2000

        Dim dr As DbDataReader = Nothing

        Try
            Dim DefaultText As String = ""
            Dim List As New Generic.List(Of String)

            With List
                .Clear()
                If AdditionalItem <> "" Then .Add(AdditionalItem)
                Dim value As String = ""
                Dim spt As New Generic.Dictionary(Of String, String)
                spt.Add("<Null>", "Null value")

                If DescriptionFieldName <> "" Then
                    dr = DB.ExecuteReader("SELECT DISTINCT {0},{1} FROM {2} ORDER BY {0}", KeyFieldName, DescriptionFieldName, FullTableName(SupportTableName))
                    If dr Is Nothing Then Exit Sub
                    While dr.Read
                        If Not dr.IsDBNull(0) Then
                            value = TestNull(dr.GetValue(1), "")
                            spt.Add(dr.GetString(0), value)
                            If spt.Count > MAXLISTSIZE Then spt.Add("Too many items...", "") : Exit While
                        End If
                    End While
                    dr.Close()
                Else
                    dr = DB.ExecuteReader("SELECT DISTINCT {0} FROM {1} ORDER BY {0}", KeyFieldName, FullTableName(SupportTableName))
                    If dr Is Nothing Then Exit Sub
                    While dr.Read
                        If Not dr.IsDBNull(0) Then
                            spt.Add(dr.GetString(0), "")
                            If spt.Count > MAXLISTSIZE Then spt.Add("Too many items...", "") : Exit While
                        End If
                    End While
                    dr.Close()
                End If

                Dim IsDataTable As Boolean = DataTablename <> "" And Not DataTablename.Equals(SupportTableName, StringComparison.OrdinalIgnoreCase)
                If IsDataTable Then 'get distinct values from data table too
                    DB.CommandTimeoutOverride = 5
                    dr = DB.ExecuteReader("SELECT DISTINCT Dta.{0} FROM {1} Dta {2} ORDER BY {0}", KeyFieldName, FullTableName(DataTablename), WhereClause)
                    While dr IsNot Nothing AndAlso dr.Read
                        Dim key As String = ""
                        key = TestNull(dr.GetValue(0), "<Null>")
                        If Not spt.TryGetValue(key, value) Then value = "(Not Found)"
                        Dim DisplayText As String = String.Format("{0} - {1}", key, value)
                        .Add(DisplayText)
                        If .Count > MAXLISTSIZE Then .Add("Too many items...") : Exit While
                        If DefaultKey IsNot Nothing AndAlso key.Equals(DefaultKey, StringComparison.OrdinalIgnoreCase) Then DefaultText = DisplayText
                    End While
                    If dr IsNot Nothing Then dr.Close()
                    If spt.Count > 0 Then
                        'also add item in case there are null values
                        Dim NumNull As Integer = DB.ExecuteScalar("SELECT COUNT(Dta.*) FROM {0} Dta WHERE {1} IS NULL {2}", DataTablename, KeyFieldName, WhereClause.ToUpper.Replace("WHERE", "AND"))
                        If NumNull > 0 Then .Add("<NULL>")
                    End If
                Else
                    If spt.Count <> 0 Then 'fill with only support table items
                        For Each kv As Generic.KeyValuePair(Of String, String) In spt
                            Dim DisplayText As String
                            If kv.Value <> "" Then
                                DisplayText = String.Format("{0} - {1}", kv.Key, kv.Value)
                            Else
                                DisplayText = kv.Key
                            End If
                            .Add(DisplayText)
                            If DefaultKey IsNot Nothing AndAlso kv.Key.Equals(DefaultKey, StringComparison.OrdinalIgnoreCase) Then DefaultText = DisplayText
                        Next
                    End If
                End If
            End With

            If TypeOf List_ComboBox Is Generic.List(Of String) Then
                List_ComboBox = List
            Else 'is list or combobox
                With List_ComboBox
                    .items.clear()
                    .items.addrange(List.ToArray)
                    If DefaultKey Is Nothing Then 'default not requested; set to first item
                        .SelectedIndex = IIf(.Items.Count = 0, -1, 0)
                    Else
                        If DefaultText = "" Then 'default requested but couldn't be found; add to list
                            .items.add(DefaultKey)
                            .text = DefaultKey
                        Else
                            .Text = DefaultText
                        End If
                    End If
                End With
            End If
        Catch ex As Exception
            Throw
        Finally
            If dr IsNot Nothing Then dr.Close()
            DB.CloseConnection()
        End Try
    End Sub

    ''' <summary>
    ''' Helper function to retrieve key value from list created using FillPickList
    ''' </summary>
    ''' <param name="cbo_text">Combobox, Listbox, or generic list of string objects</param>
    ''' <returns>Selected key value</returns>
    Public Function GetPickList(ByRef cbo_text As Object) As String
        Dim s As String
        If TypeOf cbo_text Is String Then
            s = cbo_text
        Else
            s = cbo_text.text
        End If
        If s.Contains(" - ") Then
            Return s.Substring(0, s.IndexOf(" - "))
        Else
            Return s
        End If
    End Function

    ''' <summary>
    ''' Helper function to set list item based on key value from list created using FillPickList
    ''' </summary>
    ''' <param name="cbo_text">Combobox, Listbox, or generic list of string objects</param>
    ''' <param name="KeyValue">Desired key to set</param>
    Public Sub SetPickList(ByRef cbo_text As Object, KeyValue As String)
        If TypeOf cbo_text Is String Then Throw New InvalidCastException("SetPickList cannot be called with string object")
        KeyValue = KeyValue.Trim
        For Each s As String In cbo_text.items
            If s.StartsWith(KeyValue & " - ") Or s = KeyValue Then
                cbo_text.text = s
                Exit Sub
            End If
        Next
        'couldn't find it, so add to list
        cbo_text.items.add(KeyValue)
        cbo_text.text = KeyValue
    End Sub

    ''' <summary>
    ''' Determine the number of records in a table that were owned by or selected by a specific user by starting a new thread
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <param name="OwnerName">Name of user who owns records</param>
    ''' <param name="SelectedBy">Name of user who selected records</param>
    ''' <returns>Number of records (if timeout occurs, will return -1)</returns>
    Public Overridable Function GetRecordCount(TableName As String, Optional OwnerName As String = "", Optional SelectedBy As String = "") As Integer
        Dim where As String = IIf(OwnerName = "" And SelectedBy = "", "", "WHERE ")
        If OwnerName <> "" Then where &= String.Format("(Owner = '{0}' OR Owner IS NULL)", OwnerName)
        If GetTableType(TableName) = enumTableTypes.Temporary AndAlso SelectedBy <> "" Then where &= IIf(OwnerName = "", "", " AND ") & String.Format("Selected_By = '{0}'", SelectedBy)
        Try
            'Dim NumRec As Integer = DB.ExecuteScalar("SELECT COUNT(*) FROM {0} {1}", FullTableName(TableName), where)
            Return DB.ExecuteSQL(clsDB.enumSQLType.Scalar, "Getting record count", "SELECT COUNT(*) FROM {0} Dta {1}", FullTableName(TableName), where)
        Catch ex As DbException
            Return -1
        End Try
    End Function

    ''' <summary>
    ''' Return list of tablenames that match list of table types (or Working if empty list) (converted to Proper case)
    ''' </summary>
    ''' <param name="TableType">List of table types (if empty, returns list of working tables); use type enumTableTypes</param>
    ''' <returns>List of table names</returns>
    Public Function GetTableList(ByVal ParamArray TableType() As enumTableTypes) As List(Of String)
        Dim lst As New Generic.List(Of String)
        If TableType.Length = 0 Then TableType = New enumTableTypes() {enumTableTypes.Working}
        For Each tt As enumTableTypes In TableType
            Dim tblList As Generic.List(Of String) = DB.GetTableViewList
            For Each tbl As String In tblList
                If Not (tbl.ToUpper.StartsWith(TempPrefix.ToUpper) Or tbl.ToUpper.StartsWith(DataPrefix.ToUpper)) Then
                    If TableType.Length = 0 Then
                        If GetTableType(tbl) = enumTableTypes.Working And Not lst.Contains(tbl) Then lst.Add(tbl)
                    Else
                        If GetTableType(tbl) = tt And Not lst.Contains(tbl) Then lst.Add(tbl)
                    End If
                End If
            Next
        Next
        Return lst
    End Function

    ''' <summary>
    ''' Return list of all data and support tablenames; only working tables assigned to this project will be included 
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Function GetTableListAll() As Generic.List(Of String)
        Dim lst As New Generic.List(Of String)
        Dim tbllist = DB.GetTableList

        If tbllist IsNot Nothing Then
            For Each tbl As String In DB.GetTableViewList()
                If GetTableType(tbl) = enumTableTypes.Working Then lst.Add(tbl)
            Next

            For tt As enumTableTypes = enumTableTypes.Master To enumTableTypes.Settings
                For Each tbl As String In DB.GetTableViewList
                    If GetTableType(tbl) = tt And Not lst.Contains(tbl) Then lst.Add(tbl)
                Next
            Next
        End If

        Return lst
    End Function

    ''' <summary>
    ''' Given the table name, return which type of table it is
    ''' </summary>
    ''' <param name="_TableName">Standard table name (no brackets, etc.)</param>
    Public Function GetTableType(_TableName As String) As enumTableTypes
        _TableName = _TableName.Replace("[", "").Replace("]", "")

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            ((_TableName.Equals(clsProject.TempData, StringComparison.OrdinalIgnoreCase) OrElse
              _TableName.Equals(clsProject.TempView, StringComparison.OrdinalIgnoreCase) OrElse
             (QueryOption = enumQueryOption.Subselect AndAlso _TableName.StartsWith("(")))) Then Return enumTableTypes.Temporary

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            _TableName.ToLower.Contains(".dbo.") Then Return enumTableTypes.HiddenData

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            (_TableName.Equals(clsProject.RptData, StringComparison.OrdinalIgnoreCase) Or
             _TableName.Equals(clsProject.ExtData, StringComparison.OrdinalIgnoreCase)) Then Return enumTableTypes.HiddenData

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            _TableName.StartsWith(clsProject.OtherPrefix, StringComparison.OrdinalIgnoreCase) Then Return enumTableTypes.Other

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            _TableName.Equals(TableName(enumTableTypes.Settings), StringComparison.OrdinalIgnoreCase) Then Return enumTableTypes.Settings

        For i As enumTableTypes = enumTableTypes.Stations To enumTableTypes.Tracking
            If _TableName.Equals(TableName(i), StringComparison.OrdinalIgnoreCase) Then Return i
        Next

        If _TableName.Trim = "" Then Return enumTableTypes.Working

        If _TableName.Equals(MasterTableName, StringComparison.OrdinalIgnoreCase) Then Return enumTableTypes.Master

        If DB.DatabaseType = clsDB.enumDBType.Paradox AndAlso Not WorkingDir.Equals(MasterDir, StringComparison.OrdinalIgnoreCase) Then 'see if table is in master dir
            If My.Computer.FileSystem.FileExists(MasterDir & _TableName & ".db") Then Return enumTableTypes.Master
        End If
        Return enumTableTypes.Working
    End Function

    ''' <summary>
    ''' Is specified table a support table?
    ''' </summary>
    ''' <param name="_TableName">Name of table (case ignored)</param>
    ''' <returns>True if is Support table</returns>
    Public Function IsSupportTable(_TableName As String) As Boolean
        Select Case GetTableType(_TableName)
            Case enumTableTypes.Stations To enumTableTypes.Tracking
                Return True
            Case Else
                Return False
        End Select
    End Function

    ''' <summary>
    ''' Is specified table a Working or Master data table?
    ''' </summary>
    ''' <param name="_TableName">Name of table (case ignored)</param>
    ''' <returns>True if is data table</returns>
    Public Function IsDataTable(_TableName As String) As Boolean
        Select Case GetTableType(_TableName)
            Case enumTableTypes.Working To enumTableTypes.Master
                Return True
            Case Else
                Return False
        End Select
    End Function

    ''' <summary>
    ''' Get the table structure based on the table name
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Data table structured like desired table</returns>
    Public Function GetTableTemplate(ByVal TableName As String) As DataTable
        If GetTableType(TableName) = enumTableTypes.Other Then Return Nothing Else Return dsWRDB.Tables(GetTableType(TableName))
    End Function

    ''' <summary>
    ''' Get the table structure based on the table type
    ''' </summary>
    ''' <param name="TableType">Type of table</param>
    ''' <returns>Data table structured like desired table</returns>
    Public Function GetTableTemplate(ByVal TableType As enumTableTypes) As DataTable
        Return dsWRDB.Tables(TableType)
    End Function

    ''' <summary>
    ''' Copy and rename table in current database, or copy from another project
    ''' </summary>
    ''' <param name="DestTable">Name of destination table</param>
    ''' <param name="SourceTable">Name of source table</param>
    ''' <param name="SourceProject">Source project (if Nothing, assumes current project, and cannot update)</param>
    ''' <param name="Update">True if destination table should be updated (requires slower export/import even if in same database)</param>
    ''' <returns>True if successful</returns>
    Public Function CopyTable(ByRef DestTable As String, ByVal SourceTable As String, Optional ByRef SourceProject As clsProject = Nothing, Optional ByVal Update As Boolean = False, Optional ByVal OverwriteIfExists As Boolean = True, Optional ByVal AutoRename As Boolean = False) As Boolean

        DestTable = DB.GetValidTableName(DestTable)
        If DestTable = "" Then Return False
        Dim TableType As enumTableTypes = GetTableType(SourceTable)

        If SourceProject Is Nothing And Not Update Then 'copy table within the same project--must have unique tablename and destination must be empty
            If DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    DeleteTable(DestTable)
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            If Not CreateTable(TableType, DestTable) Then Return False
            UpdateTrackingRecord(DestTable, GetRecordCount(DestTable), True)
            Return DB.AppendTable(DestTable, SourceTable)
        Else 'copy tables across projects (or do append/update of existing table)--do export then import
            If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)
            Dim TextFile As String = String.Format("{0}\{1}.txt", TempPath, SourceTable)
            If SourceProject Is Nothing Then SourceProject = Me
            If Not SourceProject.ExportTable(SourceTable, TextFile) Then Return False
            If Not Update AndAlso DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    If HasTablePermissions Then
                        DeleteTable(DestTable)
                    Else
                        If DB.ExecuteNonQuery($"DELETE FROM {FullTableName(DestTable)}") = -2 Then Exit Function 'no permissions, so assume schema is OK and just purge the table
                    End If
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            If Not DB.TableExists(DestTable) AndAlso Not CreateTable(TableType, DestTable) Then Return False
            Return ImportTable(DestTable, TextFile, Update)
        End If
    End Function

    ''' <summary>
    ''' Copy and rename table in current database, or copy from another project
    ''' </summary>
    ''' <param name="DestTable">Name of destination table</param>
    ''' <param name="SourceTable">Name of source table</param>
    ''' <param name="SourceProject">Source project (if Nothing, assumes current project, and cannot update)</param>
    ''' <param name="Update">True if destination table should be updated (requires slower export/import even if in same database)</param>
    ''' <returns>True if successful</returns>
    Public Function CopyWorkingTable(ByRef DestTable As String,
                                     SourceTable As String,
                                     ByRef SourceProject As clsProject,
                                     Update As Boolean,
                                     OverwriteIfExists As Boolean,
                                     AutoRename As Boolean,
                                     OwnerName As String) As Boolean

        DestTable = DB.GetValidTableName(DestTable)
        If DestTable = "" Then Return False
        Dim TableType As enumTableTypes = GetTableType(SourceTable)

        If SourceProject Is Nothing And Not Update Then 'copy table within the same project--must have unique tablename and destination must be empty
            If DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    DeleteTable(DestTable)
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            If Not CreateTable(TableType, DestTable) Then Return False
            UpdateTrackingRecord(DestTable, GetRecordCount(DestTable), True)
            If DB.AppendTable(DestTable, SourceTable) Then
                If Not String.IsNullOrEmpty(OwnerName) Then 'delete all records that are NOT specified owner
                    If DB.ExecuteNonQuery($"DELETE FROM {FullTableName(DestTable)} WHERE OWNER<>'{OwnerName}'") = -2 Then Return False
                End If
            Else
                Return False
            End If
        Else 'copy tables across projects (or do append/update of existing table)--do export then import
            If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)
            Dim TextFile As String = String.Format("{0}\{1}.txt", TempPath, SourceTable)
            If SourceProject Is Nothing Then SourceProject = Me
            Dim sql As String = ""
            If Not String.IsNullOrEmpty(OwnerName) Then sql = String.Format("SELECT * FROM {0} WHERE OWNER='{1}'", SourceTable, OwnerName)
            If Not SourceProject.ExportTable(SourceTable, TextFile, sql) Then Return False
            If Not Update AndAlso DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    If HasTablePermissions Then
                        DeleteTable(DestTable)
                    Else
                        If DB.ExecuteNonQuery($"DELETE FROM {FullTableName(DestTable)}") = -2 Then Return False 'no permissions, so assume schema is OK and just purge the table
                    End If
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            If Not DB.TableExists(DestTable) AndAlso Not CreateTable(TableType, DestTable) Then Return False
            Return ImportTable(DestTable, TextFile, Update)
        End If
    End Function

    ''' <summary>
    ''' Import data from tab-delimited file into table; if table exists, will be emptied first (or updated), if not will be created
    ''' The file format is assumed to be identical to the export format
    ''' </summary>
    ''' <param name="TableName">New table to import data into</param>
    ''' <param name="ImportFileName">Tab-delimited text file to import data from</param>
    ''' <param name="Append_Update">If false, existing table will be emptied first; otherwise will be appended to or replaced on record-by-record basis (if indexed)</param>
    ''' <param name="AppendOnly">If Append_Update is true and AppendOnly is true, will not replace existing records with new records</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Function ImportTable(ByRef TableName As String, ByVal ImportFileName As String, Optional ByVal Append_Update As Boolean = False, Optional ByVal AppendOnly As Boolean = False) As Boolean
        If DB.ImportTable(TableName, ImportFileName, GetTableTemplate(TableName), Append_Update, AppendOnly) Then
            If GetTableType(TableName) = enumTableTypes.Working Then
                RaiseEvent Progress(DB, 0, 100, "Updating table; please stand by...")
                UpdateTrackingRecord(TableName, DB.GetRecordCount(TableName), True)
                UpdateOwnerField(TableName)
                RaiseEvent Progress(DB, 100, 100, "")
            End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Export table to tab-delimited file (wrapper for clsGenericData that resets paradox connection first)
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="ExportFileName">Name of file to export to (if blank, will export to temp file and return name here; caller must place in clipboard and delete tempfile)</param>
    ''' <param name="SQLString">If provided, uses SQL statement to select data to be exported</param>
    ''' <param name="UseCurrentCulture">By default will export using invariant culture, but can apply current culture setting if export from main menu (instead of archiving)</param>
    ''' <returns>True if successful</returns>
    Public Function ExportTable(ByRef TableName As String, ByRef ExportFileName As String, Optional ByVal SQLString As String = "", Optional UseCurrentCulture As Boolean = False) As Boolean
        Return DB.ExportTable(TableName, ExportFileName, SQLString, UseCurrentCulture)
    End Function

    ''' <summary>
    ''' Determine if specified table exists
    ''' </summary>
    ''' <param name="TableType">The type of table to check</param>
    ''' <param name="WorkingTableName">The name of the Working table to check</param>
    ''' <returns>True if it exists</returns>
    ''' <remarks>TableName must be omitted unless checking for Working table</remarks>
    Public Function TableExists(TableType As enumTableTypes, Optional WorkingTableName As String = "") As Boolean
        Dim TblName As String
        Select Case TableType
            Case enumTableTypes.Working
                TblName = DB.GetValidTableName(WorkingTableName)
                If TblName = "" Then Return False
            Case enumTableTypes.Master
                TblName = MasterTableName
            Case Else
                TblName = TableName(TableType)
        End Select
        '3/2/16 allow all data and support tables to be views
        Select Case TableType
            Case enumTableTypes.Working To enumTableTypes.Legacy
                Return DB.TableViewExists(TblName)
            Case Else
                Return DB.TableExists(TblName)
        End Select
    End Function

    ''' <summary>
    ''' Create an empty standard table (table type is taken from table name)
    ''' </summary>
    ''' <param name="TableName">The name of table to create</param>
    ''' <returns>True if successful</returns>
    Public Function CreateTable(ByRef TableName As String) As Boolean
        Return DB.CreateTable(TableName, GetTableTemplate(TableName))
    End Function

    ''' <summary>
    ''' Create an empty standard table
    ''' </summary>
    ''' <param name="TableType">The type of table to create</param>
    ''' <param name="NewTableName">If TableType is Working, must supply this table name; can optionally supply for other table types. If table needs to be renamed to conform to database rules, returns that new name.</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Paradox table will be placed in their correct folders</remarks>
    Public Function CreateTable(TableType As enumTableTypes, Optional ByRef NewTableName As String = "") As Boolean

        Dim TblName As String
        Select Case TableType
            Case enumTableTypes.Working
                TblName = DB.GetValidTableName(NewTableName)
                If TblName = "" Then Return False
            Case enumTableTypes.Master
                If NewTableName <> "" Then 'sometimes need to create a temp table in the master folder
                    TblName = NewTableName
                Else
                    TblName = MasterTableName
                End If
            Case Else
                If NewTableName = "" Then TblName = TableName(TableType) Else TblName = NewTableName
        End Select

        'get table structure from dataset

        If DB.CreateTable(TblName, GetTableTemplate(TableType)) Then
            If TableType = enumTableTypes.Working And Not NewTableName.Equals(TempData, StringComparison.InvariantCultureIgnoreCase) And Not NewTableName.Equals(RptData, StringComparison.InvariantCultureIgnoreCase) Then
                UpdateTrackingRecord(NewTableName, , True)
            ElseIf TableType = enumTableTypes.FIPSCodes Then 'populate with U.S. FIPS codes in text file
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\FIPS Codes.txt", GetTableTemplate(TableType))
            ElseIf TableType = enumTableTypes.WQXMethods Then 'populate with WQX methods
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\WQX Methods.txt", GetTableTemplate(TableType))
            ElseIf TableType = enumTableTypes.Validation Then 'populate with Validation items
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\Validation.txt", GetTableTemplate(TableType))
            End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Create all Support tables that don't already exist
    ''' </summary>
    ''' <returns>True if successful</returns>
    Public Function CreateAllSupportTables() As Boolean
        For TblType As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Settings
            If Not TableExists(TblType) Then
                If Not CreateTable(TblType) Then Return False
            End If
        Next
        Return True
    End Function

    Public Sub CreateAllViewScripts()
        Dim sw As New IO.StreamWriter(clsDB.DLLPath & "\CreateViews.sql")
        sw.WriteLine($"Use {DB.DatabaseName}")
        sw.WriteLine("GO")
        For TblType As clsProject.enumTableTypes = clsProject.enumTableTypes.Master To clsProject.enumTableTypes.Settings
            sw.WriteLine(DB.CreateNullViewSQL(TableName(TblType), GetTableTemplate(TblType)))
            sw.WriteLine("GO")
        Next
        sw.Close()
        sw.Dispose()
    End Sub

    Public Sub CreateAllTableScripts()
        Dim sw As New IO.StreamWriter(clsDB.DLLPath & "\CreateTables.sql")
        sw.WriteLine($"Use {DB.DatabaseName}")
        sw.WriteLine("GO")
        For TblType As clsProject.enumTableTypes = clsProject.enumTableTypes.Master To clsProject.enumTableTypes.Settings
            sw.WriteLine(DB.CreateTableSQL(TableName(TblType), GetTableTemplate(TblType)))
            sw.WriteLine("GO")
        Next
        sw.Close()
        sw.Dispose()
    End Sub

    ''' <summary>
    ''' Create all temporary tables that will be reused
    ''' </summary>
    ''' <returns>True if successful</returns>
    Public Function CreateAllTempTables() As Boolean
        Try

            CreateTableTemplates()
            If QueryOption = enumQueryOption.Table AndAlso Not DB.CheckTableStructure(TempData, GetTableTemplate(enumTableTypes.Temporary)) Then
                If DB.TableExists(TempData) Then DB.ExecuteNonQuery($"DELETE FROM {TempData}") 'empty it out first, don't want to save temp data
                If Not DB.UpgradeDataTable(TempData, GetTableTemplate(enumTableTypes.Temporary)) Then Return False
            End If
            If DB.TableExists(New String() {TempData, RptData, ExtData}) Then Return True
            If Not DB.TableExists(TempData) AndAlso Not CreateTable(TempData) Then Return False
            If Not DB.TableExists(RptData) AndAlso Not CreateTable(RptData) Then Return False
            If Not DB.TableExists(ExtData) AndAlso Not CreateTable(ExtData) Then Return False

            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    ''' <summary>
    ''' Place current user's initials into Owner field of data table
    ''' </summary>
    ''' <param name="TableName">Working or Temporary table</param>
    Public Sub UpdateOwnerField(TableName As String)
        If DB.TableExists(TableName) Then DB.ExecuteNonQuery("UPDATE {0} SET OWNER='{1}' WHERE OWNER IS NULL", DB.FullTableName(TableName), clsApplicationSettings.UserName)
    End Sub

    ''' <summary>
    ''' Delete all records in data table owned by current user
    ''' </summary>
    ''' <param name="TableName">Working or Temporary table</param>
    Public Sub DeleteOwnerRecords(TableName As String)
        If DB.TableExists(TableName) Then DB.ExecuteNonQuery("DELETE FROM {0} WHERE OWNER = '{1}'", DB.FullTableName(TableName), clsApplicationSettings.UserName)
    End Sub

    ''' <summary>
    ''' Project is being closed, so remove all temporary tables and views associated with this user
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CleanDatabase()
        If Not DB.TestConnectionQuick(CONNECTIONTIMEOUT) Then Exit Sub

        'delete records having this user's name
        If Not String.IsNullOrEmpty(TempData) Then DeleteRecordsSelectedByUser(TempData)
        If Not String.IsNullOrEmpty(RptData) Then DeleteOwnerRecords(RptData)
        If Not String.IsNullOrEmpty(ExtData) Then DeleteOwnerRecords(ExtData)

    End Sub

    Public Sub DeleteTable(ByVal TableName As String, Optional ByVal DeleteTracking As Boolean = True)
        DB.DropTable(TableName)
        If DeleteTracking AndAlso GetTableType(TableName) = enumTableTypes.Working Then
            DeleteTrackingRecord(TableName)
        End If
    End Sub

    ''' <summary>
    ''' Delete all records in working or temporary table belonging to current user
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <remarks></remarks>
    Public Sub DeleteRecordsSelectedByUser(TableName As String)
        If DB.TableExists(TableName) Then
            Dim NumRec As Integer = 0
            'deleting in chunks may be a little faster but most importantly prevents the system-managed temp table (for rollbacks) from growing too large
            Dim DeleteInChunks As Boolean = (DB.DatabaseType = clsDB.enumDBType.Oracle Or DB.DatabaseType = clsDB.enumDBType.SQL_Server)
            Dim Chunksize As Integer = 40000
            Dim sw As New Stopwatch
            sw.Start()
            If Not DeleteInChunks Then 'don't want to delete all records in a single transaction because the log file grows too large; do it in a loop
                NumRec = DB.ExecuteNonQuery("DELETE FROM {0} WHERE Selected_By='{1}'", DB.FullTableName(TableName), clsApplicationSettings.UserName)
            Else 'delete in chunks
                Dim Sql As String = ""
                Select Case DB.DatabaseType
                    Case clsDB.enumDBType.Firebird, clsDB.enumDBType.Firebird_Embedded
                        Sql = String.Format("DELETE FIRST {2} FROM {0} WHERE Selected_By='{1}'", DB.FullTableName(TableName), clsApplicationSettings.UserName, Chunksize)
                    Case clsDB.enumDBType.SQLite, clsDB.enumDBType.MySQL
                        Sql = String.Format("DELETE FROM {0} WHERE Selected_By='{1}' LIMIT {2}", DB.FullTableName(TableName), clsApplicationSettings.UserName, Chunksize)
                    Case clsDB.enumDBType.Oracle
                        Sql = String.Format("DELETE FROM {0} WHERE Selected_By='{1}' AND ROWNUM<={2}", DB.FullTableName(TableName), clsApplicationSettings.UserName, Chunksize)
                    Case Else
                        Sql = String.Format("DELETE TOP ({2}) FROM {0} WHERE Selected_By='{1}'", DB.FullTableName(TableName), clsApplicationSettings.UserName, Chunksize)
                End Select
                Do
                    NumRec = DB.ExecuteNonQuery(Sql)
                Loop Until NumRec = 0
            End If
            sw.Stop()
            Debug.WriteLine($"DeleteInChunks: {DeleteInChunks}; Chunksize: {Chunksize }; Time: {sw.Elapsed.TotalSeconds }")
        End If
    End Sub

    Public Sub RenameTable(ByVal OldName As String, ByVal NewName As String)
        With DB
            If .RenameTable(OldName, NewName, GetTableTemplate(OldName)) Then
                Select Case GetTableType(OldName)
                    Case enumTableTypes.Working : RenameTrackingRecord(OldName, NewName)
                    Case enumTableTypes.Master : MasterTableName = NewName
                End Select
            End If
        End With
    End Sub

    ''' <summary>
    ''' Determine if specified WRDB Stations shapefile is up to date
    ''' </summary>
    ''' <returns>True if is up to date</returns>
    ''' <remarks>This checks to see if attribute table contains Lat/Long fields and if they are identical to values in stations table</remarks>
    Public Function IsStationShapefileValid(ShapeFileName As String ) As Boolean
        Dim ShapePath As String = IO.Path.GetDirectoryName(ShapeFileName)
        Dim TableName As String = IO.Path.GetFileNameWithoutExtension(ShapeFileName)
        If Not My.Computer.FileSystem.FileExists(String.Format("{0}\{1}.dbf", ShapePath, TableName)) Then Return False

        Dim checksumDBF As Double = 0, checksumSta As Double = 0
        Dim NameTypeDBF As String = "", NameTypeSta As String = ""
        Dim dbf As New clsDBDBase
        With dbf
            .DatabaseType = clsDB.enumDBType.DBase
            .DataSource = ShapePath
            .CreateConnection()
            If .TableExists(TableName) Then
                Dim lst As Generic.List(Of String) = .GetColumnList(TableName)
                If Not (lst.Contains("LATITUDE") And lst.Contains("LONGITUDE")) Then Return False
            Else
                Return False
            End If
            checksumDBF = TestNull(.ExecuteScalar("SELECT SUM(Latitude)+SUM(Longitude) FROM {0}", .AddBrackets(TableName)), 0)
            Dim drDBF As DbDataReader = .ExecuteReader("SELECT Station_ID, Stat_Type FROM {0} ORDER BY Station_ID", TableName)
            While drDBF IsNot Nothing AndAlso drDBF.Read
                NameTypeDBF &= Trim(TestNull(drDBF(0), "")) & Trim(TestNull(drDBF(1), ""))
            End While
            drDBF.Close()
            .Connection.Close()
            .Dispose()
        End With
        If DB.DatabaseType = clsDB.enumDBType.Firebird Or DB.DatabaseType = clsDB.enumDBType.Firebird_Embedded Then 'doesn't support ABS function
            checksumSta = TestNull(DB.ExecuteScalar($"SELECT SUM(Latitude)+SUM(Longitude) FROM {FullTableName(enumTableTypes.Stations)} Stations WHERE Longitude IS NOT NULL and Latitude IS NOT NULL"), 0)
        Else
            checksumSta = TestNull(DB.ExecuteScalar($"SELECT SUM(Latitude)+SUM(Longitude) FROM {FullTableName(enumTableTypes.Stations)} Stations WHERE ABS(Longitude)>1 AND Latitude>1 AND ABS(Longitude)<180 AND Latitude<90"), 0)
        End If
        'also need to check that station IDs and types are identical
        Dim drSta As DbDataReader = DB.ExecuteReader($"SELECT Station_ID, Station_Type FROM {FullTableName(enumTableTypes.Stations)} WHERE LATITUDE IS NOT NULL AND LATITUDE <> 0 AND LONGITUDE IS NOT NULL AND LONGITUDE <> 0 ORDER BY Station_ID")
        While drSta IsNot Nothing AndAlso drSta.Read
            NameTypeSta &= Trim(TestNull(drSta(0), "")) & Trim(TestNull(drSta(1), ""))
        End While
        drSta.Close()

        Return Math.Abs(checksumDBF - checksumSta) < 0.01 And NameTypeDBF = NameTypeSta
    End Function

    ''' <summary>
    ''' Return full table name (with brackets and ownername prepended for Oracle)
    ''' </summary>
    ''' <param name="TableType">Standard table type</param>
    Public Function FullTableName(ByVal TableType As enumTableTypes) As String
        Return DB.FullTableName(TableName(TableType))
    End Function

    ''' <summary>
    ''' Return full table name (with brackets and ownername prepended for Oracle)
    ''' </summary>
    ''' <param name="TableName">Table name</param>
    Public Function FullTableName(ByVal TableName As String) As String
        If QueryOption = enumQueryOption.Subselect Then Return TableName Else Return DB.FullTableName(TableName)
    End Function

    ''' <summary>
    ''' Return standard table name
    ''' </summary>
    ''' <param name="Tabletype">Enumerated table type</param>
    Public Function TableName(ByVal Tabletype As enumTableTypes) As String
        Select Case Tabletype
            Case enumTableTypes.Temporary : Return clsProject.TempData
            Case enumTableTypes.Working : Debug.Assert(False, "Cannot pass working table as argument") : Return ""
            Case enumTableTypes.Master : Return MasterTableName
            Case Else
                Return _TableName(Tabletype)
        End Select
    End Function

    ''' <summary>
    ''' Return standard table name without brackets, etc.
    ''' </summary>
    ''' <param name="FullTableName">Full table name with brackets, etc.</param>
    Public Function TableName(FullTableName As String) As String
        Return DB.TableName(FullTableName)
    End Function

#Region "Routines to keep tracking table synchronized with project"

    ''' <summary>
    ''' When creating tables, entries in the Tracking table may need to be updated
    ''' </summary>
    ''' <param name="WorkingTable">Name of working table</param>
    ''' <param name="RecordCount">If the record count is already known, pass it here</param>
    ''' <param name="HasBeenUpdated">If true, will reset Create_Update field</param>
    ''' <param name="TrackID">If not default, will reset Track_ID field</param>
    Public Sub UpdateTrackingRecord(WorkingTable As String, Optional RecordCount As Integer = Integer.MinValue, Optional HasBeenUpdated As Boolean = False, Optional TrackID As Integer = Integer.MinValue)
        Dim da As DbDataAdapter = Nothing
        Dim dt As DataTable = Nothing
        Try
            If GetTableType(WorkingTable) <> enumTableTypes.Working And GetTableType(WorkingTable) <> enumTableTypes.Master Then Exit Sub

            'load all records into data table

            If DataSet IsNot Nothing AndAlso DataSet.Tables.Contains("Tracking") Then DataSet.Tables.Remove("Tracking")

            da = DB.AddTable(DataSet, "Tracking", "SELECT * FROM Tracking WHERE ({1}(Temp_DB_Name)='{0}' OR {1}(Temp_DB_Name)='{0}.DB')", DB.RemoveBrackets(WorkingTable).ToUpper, DB.UCase)
            dt = DataSet.Tables("Tracking")

            Dim row As DataRow
            Select Case dt.Rows.Count
                Case 0
                    row = dt.NewRow
                Case Else
                    row = dt.Rows(0)
            End Select

            'if more than one matching record, delete all but first to avoid constraint violation
            For r As Integer = dt.Rows.Count - 1 To 1 Step -1
                dt.Rows.RemoveAt(r)
            Next

            With row
                .Item("Temp_DB_Name") = DB.RemoveBrackets(WorkingTable)
                .Item("Master_DB_Name") = DB.RemoveBrackets(MasterTableName)
                SetIfNull(.Item("TableGroup"), "")
                If .Item("Master_DB_Name").ToString.EndsWith(".DB", StringComparison.OrdinalIgnoreCase) Then .Item("Master_DB_Name") = .Item("Master_DB_Name").ToString.Replace(".DB", "").Replace(".db", "")
                If RecordCount <> Integer.MinValue Then .Item("Record_Count") = RecordCount
                If HasBeenUpdated Then .Item("Create_Update") = Now
                If TrackID <> Integer.MinValue Then .Item("Track_ID") = TrackID
            End With

            If dt.Rows.Count = 0 Then dt.Rows.Add(row)

            Dim numrows As Integer = da.Update(dt)
        Catch ex As Exception
            'may fail if constraint violation (change working or master table to same as another record)
            Debug.Print("Unable to update tracking table!")
        Finally
            If dt IsNot Nothing Then dt.Dispose()
            If da IsNot Nothing Then da.Dispose()
        End Try
    End Sub

    Public Function GetTrackingRecordCount(WorkingTable As String) As Integer
        Dim dr As DbDataReader = Nothing
        Try
            Dim NumRecords As Integer = 0
            If Not WorkingTable.StartsWith("(") Then NumRecords = TestNull(DB.ExecuteScalar("SELECT Record_Count FROM {0} WHERE ({1}(Temp_DB_Name)='{2}')", FullTableName(enumTableTypes.Tracking), DB.UCase, DB.RemoveBrackets(WorkingTable).ToUpper), -1)
            If NumRecords = 0 Then
                NumRecords = DB.GetRecordCount(WorkingTable)
                UpdateTrackingRecord(WorkingTable, NumRecords)
                Return NumRecords
            ElseIf NumRecords > 0 Then
                Return NumRecords
            Else
                Return -1
            End If
        Catch ex As Exception
            Return 0
        Finally
            If dr IsNot Nothing Then dr.Close()
        End Try
    End Function

    Public Sub GetTrackingRecord(ByVal WorkingTable As String, ByRef RecordCount As Integer, ByRef LastUpdated As Date)
        Dim dr As DbDataReader = DB.ExecuteReader("SELECT Record_Count,Create_Update FROM {0} WHERE ({1}(Temp_DB_Name)='{2}')", FullTableName(enumTableTypes.Tracking), DB.UCase, DB.RemoveBrackets(WorkingTable).ToUpper)
        If dr IsNot Nothing AndAlso dr.Read Then
            RecordCount = TestNull(dr.GetValue(0), -1)
            LastUpdated = TestNull(dr.GetValue(1), "1/1/1900")
        Else
            RecordCount = -1
            LastUpdated = "1/1/1900"
        End If
        dr.Close()
    End Sub

    ''' <summary>
    ''' When deleting tables, entries in the Tracking table may need to be updated (will delete all import records too as well as journal entries)
    ''' </summary>
    Public Sub DeleteTrackingRecord(WorkingTable As String)
        DB.ExecuteNonQuery("DELETE FROM {2} WHERE ({1}(Temp_DB_Name)='{0}' OR {1}(Temp_DB_Name)='{0}.DB')", DB.RemoveBrackets(WorkingTable).ToUpper, DB.UCase, FullTableName(enumTableTypes.Tracking))
        DB.ExecuteNonQuery("DELETE FROM {2} WHERE {1}(Table_Name)='{0}'", WorkingTable.ToUpper, DB.UCase, FullTableName(enumTableTypes.Journal))
    End Sub

    ''' <summary>
    ''' When renaming table, entries in the Tracking table must be updated (Journal will be updated too)
    ''' </summary>
    Public Sub RenameTrackingRecord(OldName As String, NewName As String)
        DB.ExecuteNonQuery("UPDATE {3} SET Temp_DB_Name='{1}' WHERE ({2}(Temp_DB_Name)='{0}' OR {2}(Temp_DB_Name)='{0}.DB')", DB.RemoveBrackets(OldName).ToUpper, DB.RemoveBrackets(NewName), DB.UCase, FullTableName(enumTableTypes.Tracking))
        DB.ExecuteNonQuery("UPDATE {3} SET Table_Name='{1}' WHERE {2}(Table_Name)='{0}'", OldName.ToUpper, NewName, DB.UCase, FullTableName(enumTableTypes.Journal))
    End Sub

    ''' <summary>
    ''' Find all Tracking records that match existing tables and fix them; delete for non-existent tables; add for new ones
    ''' </summary>
    Public Sub SynchTrackingRecords()
        Try
            'load all records into data table
            Dim da As DbDataAdapter = DB.AddTable(DataSet, "Tracking", "SELECT * FROM Tracking")
            Dim dt As DataTable = DataSet.Tables("Tracking")

            Dim TableList As Generic.List(Of String) = GetTableList(enumTableTypes.Working)

            'look at each record in tracking table and update with existing table info (delete if no table)

            For Each row As DataRow In dt.Rows
                With row
                    Dim TableName As String = TestNull(.Item("Temp_DB_Name").ToString, "")
                    Dim found As Boolean = False
                    For Each s As String In TableList
                        If s.ToUpper = TableName.ToUpper.Replace(".DB", "") Then
                            found = True
                            SetIfNull(.Item("Master_DB_Name"), DB.RemoveBrackets(MasterTableName))
                            If .Item("Master_DB_Name").ToString.EndsWith(".DB", StringComparison.OrdinalIgnoreCase) Then .Item("Master_DB_Name") = .Item("Master_DB_Name").ToString.Replace(".DB", "").Replace(".db", "")
                        End If
                    Next
                    If Not found Then 'is in tracking table but does not exist
                        .Delete()
                    End If
                End With
            Next

            da.Update(dt)
            dt.AcceptChanges()

            'see if there are any working tables that are not in the Tracking table--then add

            Dim rowcount As Integer = dt.Rows.Count

            For Each s As String In TableList
                Dim found As Boolean = False
                For i As Integer = 0 To rowcount - 1 'only search through original set of rows
                    Dim row As DataRow = dt.Rows(i)
                    With row
                        Dim TableName As String = TestNull(.Item("Temp_DB_Name").ToString, "")
                        If s.ToUpper = TableName.ToUpper.Replace(".DB", "") Then found = True
                    End With
                Next
                If Not found And GetTableType(s) = enumTableTypes.Working Then 'exists but is not in Tracking table
                    Dim row As DataRow = dt.NewRow
                    With row
                        SetIfNull(.Item("TableGroup"), IIf(DB.DatabaseType = clsDB.enumDBType.Oracle, "New Group", ""))
                        SetIfNull(.Item("Temp_DB_Name"), DB.RemoveBrackets(s))
                        SetIfNull(.Item("Master_DB_Name"), DB.RemoveBrackets(MasterTableName))
                    End With
                    dt.Rows.Add(row)
                End If
            Next

            If Not DB.OpenConnection Then Exit Sub
            Dim numrows As Integer = da.Update(dt)
        Catch ex As Exception
            Throw New ApplicationException("Unable to completely synchronize Tracking records to existing Working tables. It may be necessary to manually edit the Tracking table, resetting the Project Name, Master or Working Table names, and/or deleting records referring to non-existent tables.", ex)
        End Try
    End Sub

#End Region

    ''' <summary>
    ''' When initialized, indicates if this user has add/delete/alter permissions on this database
    ''' </summary>
    Public Property HasTablePermissions As Boolean

    Protected Overrides Sub Finalize()
        'note: may make more sense to clear temporary tables, etc. here rather than clsWRDB.close, but will do there so can test for multiple instances of application.
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' Raises event so that caller (which may have UI) can report progress
    ''' </summary>
    Private Sub DB_Progress(ByRef DB As clsDB, Value As Long, MaxValue As Long, ProgressText As String) Handles DB.Progress
        RaiseEvent Progress(DB, Value, MaxValue, ProgressText)
    End Sub

    ''' <summary>
    ''' Will create a temporary WRDB project for a non-client/server data provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ProviderType">Standard provider type</param>
    ''' <param name="DataSource">Name of embedded database file or directory for paradox, dbase, txt, etc. files</param>
    Public Function CreateTempProject(ByVal ProviderType As clsDB.enumDBType, ByVal DataSource As String) As Boolean
        Try
            DB = clsDB.GetDataProvider(ProviderType, DataSource)
            HasTablePermissions = DB.HasTablePermissions
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project using the Paradox type that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="_WorkingDir">Working directory for WRDB 4 project</param>
    ''' <param name="_MasterDir">Masterdirectory for WRDB 4 project</param>
    ''' <param name="_SupportDir">Support directory for WRDB 4 project</param>
    ''' <param name="_SupportDirProj">Project support directory for WRDB 4 project</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ByVal _WorkingDir As String, ByVal _MasterDir As String, ByVal _SupportDir As String, ByVal _SupportDirProj As String) As Boolean
        Try
            WorkingDir = _WorkingDir
            MasterDir = _MasterDir
            SupportDir = _SupportDir
            SupportDirProject = _SupportDirProj
            DB = clsDB.GetDataProvider(clsDB.enumDBType.Paradox)
            HasTablePermissions = DB.HasTablePermissions
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project for a client/server provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ProviderType">Standard provider type</param>
    ''' <param name="ServerName">Name of server</param>
    ''' <param name="DatabaseName">Name of database</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ProviderType As clsDB.enumDBType, ServerName As String, DatabaseName As String) As Boolean
        Try
            DB = clsDB.GetDataProvider(ProviderType, ServerName, DatabaseName)
            HasTablePermissions = DB.HasTablePermissions
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project for a client/server provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ExternalDB">Previously configured database</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ByVal ExternalDB As clsDB) As Boolean
        Try
            DB = ExternalDB.Clone
            HasTablePermissions = DB.HasTablePermissions
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If DataSet IsNot Nothing Then DataSet.Dispose() : DataSet = Nothing
                If dsWRDB IsNot Nothing Then dsWRDB.Dispose() : dsWRDB = Nothing
                If DB IsNot Nothing Then DB.Dispose() : DB = Nothing
            End If
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

